/**
 * 
 */
package dao;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import dao.AbstractJdbcDao;
import dao.JdbcRoleDao;
import dao.UserDao;
import entries.Role;
import entries.User;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * @author zharikhin
 *
 */
public class JdbcUserDao extends AbstractJdbcDao implements UserDao {

    private final static Log LOGGER = LogFactory.getLog(JdbcUserDao.class);

    Connection conn;
    ResultSet result;
    PreparedStatement prepSt;
    private String property = "properties.xml";

    public JdbcUserDao() {

        propertyFile = property;

        try {
            props.loadFromXML(this.getClass().getClassLoader().getResourceAsStream(propertyFile));
            driverName = props.getProperty("driverName");
            url = props.getProperty("url");
            login = props.getProperty("login");
            password = props.getProperty("password");
        } catch (IOException e) {
            LOGGER.error("IOException", e);
        }
    }

    public JdbcUserDao (String propertyFile){
/*-*/        super(propertyFile);
    }

    public void create(User user)  throws SQLException, ClassNotFoundException{
        
        try{
            conn = createConnection();
            prepSt = conn.prepareStatement("insert into USER (LOGIN," +
            		" PASSWRD, EMAIL, FRST_NAME, LST_NAME, BIRTHD, ROLE_ID)" +
            		"values (?,?,?,?,?,?,?)");
            prepSt.setString(1, user.getLogin());
            prepSt.setString(2, user.getPassword());
            prepSt.setString(3, user.getEmail());
            prepSt.setString(4, user.getFirstName());
            prepSt.setString(5, user.getLastName());
            prepSt.setDate(6, user.getBirthDay());

            PreparedStatement stmt = conn.prepareStatement("select * from ROLE where ROLE_ID=?");
            stmt.setLong(1, user.getRole().getId());
            ResultSet roleResult = stmt.executeQuery();
            Role role = new Role();

            while(roleResult.next()){
                role.setId(roleResult.getLong("ROLE_ID"));
                role.setName(roleResult.getString("ROLE_NAME"));
            }
            prepSt.setLong(7, role.getId());

            prepSt.execute();
        }
        catch(SQLException e){
            LOGGER.error("SQLException", e);
        }
        finally{

            if (prepSt != null){
                try {
                    prepSt.close();
                } catch (Exception e) {
                    LOGGER.error("Exception", e);
                }
            } 
            if (conn != null){
                try {
                    conn.close();
                } catch (Exception e) {
                    LOGGER.error("Exception", e);
                }
            }
        }
    }

    public void update(User user)  throws SQLException, ClassNotFoundException{
       
        try{
            conn = createConnection();

            prepSt = conn.prepareStatement("update USER SET LOGIN=?," +
                    " PASSWRD=?, EMAIL=?, FRST_NAME=?, LST_NAME=?, BIRTHD=?, ROLE_ID= ?" +
                    " where USER_ID = ?");
            prepSt.setString(1, user.getLogin());
            prepSt.setString(2, user.getPassword());
            prepSt.setString(3, user.getEmail());
            prepSt.setString(4, user.getFirstName());
            prepSt.setString(5, user.getLastName());
            prepSt.setDate(6, user.getBirthDay());

            PreparedStatement stmt = conn.prepareStatement("select * from ROLE where ROLE_ID=?");
            stmt.setLong(1, user.getRole().getId());
            ResultSet roleResult = stmt.executeQuery();
            Role role = new Role();

            while(roleResult.next()){
                role.setId(roleResult.getLong("ROLE_ID"));
                role.setName(roleResult.getString("ROLE_NAME"));
            }
            prepSt.setLong(7, role.getId());
            user.setRole(role);
            prepSt.setLong(8, user.getId());
            prepSt.execute();
        }
        catch(SQLException e){
            LOGGER.error("SQLException", e);
        }
        finally{
            if (prepSt != null){
                try {
                    prepSt.close();
                } catch (Exception e) {
                    LOGGER.error("Exception", e);
                }
            } 
            if (conn != null){
                try {
                    conn.close();
                } catch (Exception e) {
                    LOGGER.error("Exception", e);
                }
            }
        }
    }

    public void remove(User user)  throws SQLException, ClassNotFoundException{

        try{
            conn = createConnection();

            prepSt = conn.prepareStatement("delete from USER " +
            		"where USER_ID =?");
            prepSt.setLong(1, user.getId());
            prepSt.execute();
        }
        catch(SQLException e){
            LOGGER.error("SQLException", e);
        }
        finally{
            if (prepSt != null){
                try {
                    prepSt.close();
                } catch (Exception e) {
                    LOGGER.error("Exception", e);
                }
            } 
            if (conn != null){
                try {
                    conn.close();
                } catch (Exception e) {
                    LOGGER.error("Exception", e);
                }
            }
        }
        
    }

    public List<User> findAll()  throws SQLException, ClassNotFoundException{
        
        List <User> users = new ArrayList<User>();
        Statement stmt = null;
        try{            
            conn = createConnection();
            stmt = conn.createStatement();
            result = stmt.executeQuery("select * from USER");
            
            while (result.next()) {
                User user = new User();
                
                user.setId(Long.parseLong(result.getString("USER_ID")));
                user.setLogin(result.getString("LOGIN"));
                user.setPassword(result.getString("PASSWRD"));
                user.setEmail(result.getString("EMAIL"));
                user.setFirstName(result.getString("FRST_NAME"));
                user.setLastName(result.getString("LST_NAME"));
                user.setBirthDay(result.getDate("BIRTHD"));

                PreparedStatement roleStmt = conn.prepareStatement("select * from ROLE where ROLE_ID=?");
                roleStmt.setLong(1, result.getLong("ROLE_ID"));
                ResultSet roleResult = roleStmt.executeQuery();
                Role role = new Role();

                while(roleResult.next()){
                    role.setId(roleResult.getLong("ROLE_ID"));
                    role.setName(roleResult.getString("ROLE_NAME"));
                    user.setRole(role);
                }

                users.add(user);
            }
        }
        catch(SQLException e){
            LOGGER.error("SQLException", e);
        }
        finally{
            if (stmt != null){
                try {
                    stmt.close();
                } catch (Exception e) {
                    LOGGER.error("Exception", e);
                }
            } 
            if (conn != null){
                try {
                    conn.close();
                } catch (Exception e) {
                    LOGGER.error("Exception", e);
                }
            }
        }
        return users;
    }

    public User findByLogin(String login) throws SQLException, ClassNotFoundException {
        
        User user = new User();

        try{            
            conn = createConnection();
            prepSt = conn.prepareStatement("select * from USER where LOGIN=?");
            prepSt.setString(1, login);
            result = prepSt.executeQuery();
            
            while (result.next()) {
                
                user.setId(Long.parseLong(result.getString("USER_ID")));
                user.setLogin(result.getString("LOGIN"));
                user.setPassword(result.getString("PASSWRD"));
                user.setEmail(result.getString("EMAIL"));
                user.setFirstName(result.getString("FRST_NAME"));
                user.setLastName(result.getString("LST_NAME"));
                user.setBirthDay(result.getDate("BIRTHD"));

                PreparedStatement stmt = conn.prepareStatement("select * from ROLE where ROLE_ID=?");
                stmt.setLong(1, result.getLong("ROLE_ID"));
                ResultSet roleResult = stmt.executeQuery();
                Role role = new Role();

                while(roleResult.next()){
                    role.setId(roleResult.getLong("ROLE_ID"));
                    role.setName(roleResult.getString("ROLE_NAME"));
                }
                user.setRole(role);
            }
        }
        catch(SQLException e) {
            LOGGER.error("SQLException", e);
        }
        finally {
            if (prepSt != null) {
                try {
                    prepSt.close();
                } catch (Exception e) {
                    LOGGER.error("Exception", e);
                }
            } 
            if (conn != null ){
                try {
                    conn.close();
                } catch (Exception e) {
                    LOGGER.error("Exception", e);
                }
            }
        }
        return user;
    }

    public User findByEmail(String email) throws SQLException, ClassNotFoundException {
        
        User user = new User();

        try {            
            conn = createConnection();
            prepSt = conn.prepareStatement("select * from USER where EMAIL=?");
            prepSt.setString(1, email);
            result = prepSt.executeQuery();
            
            while (result.next()) {
                
                user.setId(Long.parseLong(result.getString("USER_ID")));
                user.setLogin(result.getString("LOGIN"));
                user.setPassword(result.getString("PASSWRD"));
                user.setEmail(result.getString("EMAIL"));
                user.setFirstName(result.getString("FRST_NAME"));
                user.setLastName(result.getString("LST_NAME"));
                user.setBirthDay(result.getDate("BIRTHD"));

                PreparedStatement stmt = conn.prepareStatement("select * from ROLE where ROLE_ID=?");
                stmt.setLong(1, result.getLong("ROLE_ID"));
                ResultSet roleResult = stmt.executeQuery();
                Role role = new Role();

                while(roleResult.next()){
                    role.setId(roleResult.getLong("ROLE_ID"));
                    role.setName(roleResult.getString("ROLE_NAME"));
                }
                user.setRole(role);
           }
        }
        catch(SQLException e) {
            LOGGER.error("SQLException", e);
        }
        finally{
            if (prepSt != null) {
                try {
                    prepSt.close();
                } catch (Exception e) {
                    LOGGER.error("Exception", e);
                }
            } 
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    LOGGER.error("Exception", e);
                }
            }
        }
        return user;
    }

}
